<?php
class job_announcement_model extends Zmodel{
	
	public function selectCompanyList($user_id){
		$this->load->database();
			$sql="SELECT com_id,
        			 com_name,
        			 com_phone,
        			 com_email,
        			 com_contactname,
        			 com_website,
        			 com_logo,
        			 com_logo_type
            FROM companies WHERE user_create=".$user_id;
		return $this->db->query($sql);
	}
	
	public function selectMaxCompanyID(){
		$this->load->database();
		$Z_USER = Zsession::getUserFromSession();
		$maxid=1;
		$sql="SELECT MAX(com_id) AS com_id FROM companies WHERE user_create=".$Z_USER["user_id"];
		$rst=$this->db->query($sql);
		foreach ($rst->result_array() as $record){
			$maxid=($record["com_id"]!="")?$record["com_id"]:$maxid;
		}
		return $maxid;
	}
	
	public function deleteCompany($com_id){
		$this->load->database();
		$sql="DELETE FROM companies WHERE com_id=".$com_id;
		$this->db->query($sql);
	}
	
	
	/* Category, Schedule and Location */
	public function selectCategories(){
		$this->load->database();
		$sql="SELECT * FROM categories ORDER BY cate_desc ASC";
		return $this->db->query($sql);
	}
	public function selectCategoryByTitleID($title_id){
		$this->load->database();
		$sql="SELECT T.title_id,T.cate_id,C.cate_desc FROM categories AS C INNER JOIN titles_categories AS T ON T.cate_id=C.cate_id  WHERE T.title_id=".$title_id;
		return $this->db->query($sql);
	}
	public function insertCategory($title_id,$cate_id){
		$this->load->database();
		$sql="INSERT INTO titles_categories (title_id,cate_id) VALUES(".$title_id.",".$cate_id.")";
		$this->db->query($sql);
	}
	public function updateCategory($title_id,$cate_id){
		$this->load->database();
		$sql="UPDATE titles_categories SET cate_id=".$cate_id." WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	public function deleteCategoryByTitleId($title_id){
		$this->load->database();
		$sql="DELETE FROM titles_categories WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	public function selectSchedules(){
		$this->load->database();
		$sql="SELECT * FROM schedules";
		return $this->db->query($sql);
	}
	public function selectScheduleByTitleID($title_id){
		$this->load->database();
		$sql="SELECT T.title_id,T.schedule_id,S.schedule_desc FROM schedules AS S INNER JOIN titles_schedules AS T ON T.schedule_id=S.schedule_id  WHERE T.title_id=".$title_id;
		return $this->db->query($sql);
	}
	public function insertSchedule($title_id,$schedule_id){
		$this->load->database();
		$sql="INSERT INTO titles_schedules (title_id,schedule_id) VALUES(".$title_id.",".$schedule_id.")";
		$this->db->query($sql);
	}
	public function updateSchedule($title_id,$schedule_id){
		$this->load->database();
		$sql="UPDATE titles_schedules SET schedule_id=".$schedule_id." WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	public function deleteScheduleByTitleId($title_id){
		$this->load->database();
		$sql="DELETE FROM titles_schedules WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	public function selectLocations(){
		$this->load->database();
		$sql="SELECT * FROM locations ORDER BY loc_desc ASC";
		return $this->db->query($sql);
	}
	public function selectLocationByTitleID($title_id){
		$this->load->database();
		$sql="SELECT T.title_id,T.loc_id,L.loc_desc FROM locations AS L INNER JOIN titles_locations AS T ON T.loc_id=L.loc_id  WHERE T.title_id=".$title_id;
		return $this->db->query($sql);
	}
	public function selectLocation($loc_id){
		$this->load->database();
		$sql="SELECT * FROM titles_locations WHERE loc_id=".$loc_id;
		return $this->db->query($sql);
	}
	public function insertLocation($title_id,$loc_id){
		$this->load->database();
		$sql="INSERT INTO titles_locations (title_id,loc_id) VALUES (".$title_id.",".$loc_id.")";
		$this->db->query($sql);
	}
	public function updateLocation($title_id,$loc_id){
		$this->load->database();
		$sql="UPDATE titles_locations SET loc_id=".$loc_id." WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	public function deleteLocation($loc_id){
		$this->load->database();
		$sql="DELETE FROM titles_locations WHERE loc_id=".$loc_id;
		$this->db->query($sql);
	}
	public function deleteLocationByTitleId($title_id){
		$this->load->database();
		$sql="DELETE FROM titles_locations WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	/*-- End of Category, Schedule and Location --*/
	
	
	/* Job Duties */
	public function selectDutiesByTitleID($title_id){
		$this->load->database();
		$sql="SELECT * FROM duties WHERE title_id=".$title_id;
		return $this->db->query($sql);
	}
	public function selectDuty($duty_id){
		$this->load->database();
		$sql="SELECT * FROM duties WHERE duty_id=".$duty_id;
		return $this->db->query($sql);
	}
	public function insertDuty($title_id,$duty_desc){
		$this->load->database();
		$sql="INSERT INTO duties (title_id,duty_desc) VALUES (".$title_id.",'".pg_escape_string($duty_desc)."')";
		$this->db->query($sql);
	}
	public function deleteDuty($duty_id){
		$this->load->database();
		$sql="DELETE FROM duties WHERE duty_id=".$duty_id;
		$this->db->query($sql);
	}
	public function deleteDutyByTitleId($title_id){
		$this->load->database();
		$sql="DELETE FROM duties WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	public function updateDuty($duty_id,$duty_desc){
		$this->load->database();
		$sql="UPDATE duties SET duty_desc='".pg_escape_string($duty_desc)."' WHERE duty_id=".$duty_id;
	}
	/* End of Job Duties */
	
	
	/* Job Requirements */
	public function selectRequirementsByTitleID($title_id){
		$this->load->database();
		$sql="SELECT * FROM requirements WHERE title_id=".$title_id;
		return $this->db->query($sql);
	}
	public function selectRequirement($req_id){
		$this->load->database();
		$sql="SELECT * FROM requirements WHERE req_id=".$req_id;
		return $this->db->query($sql);
	}
	public function insertRequirement($title_id,$req_desc){
		$this->load->database();
		$sql="INSERT INTO requirements (title_id,req_desc) VALUES (".$title_id.",'".pg_escape_string($req_desc)."')";
		$this->db->query($sql);
	}
	public function deleteRequirement($req_id){
		$this->load->database();
		$sql="DELETE FROM requirements WHERE req_id=".$req_id;
		$this->db->query($sql);
	}
	public function deleteRequirementByTitleId($title_id){
		$this->load->database();
		$sql="DELETE FROM requirements WHERE title_id=".$title_id;
		$this->db->query($sql);
	}
	public function updateRequirement($req_id,$req_desc){
		$this->load->database();
		$sql="UPDATE requirements SET req_desc='".pg_escape_string($req_desc)."' WHERE req_id=".$req_id;
	}
	/* End of Job Requirements */
	
	
	/* Job Title */
	public function selectTitleByAnnounID($announ_id){
		$this->load->database();
		$sql="SELECT * FROM titles WHERE announ_id=".$announ_id;
		return $this->db->query($sql);
	}
	public function deleteTitleByAnnounID($announ_id){
		$this->load->database();
		$sql="DELETE FROM titles WHERE announ_id=".$announ_id;
		$this->db->query($sql);
	}
	/* End of Job Title */
	
	/* Company */
	function insertCompany($name,$desc,$address,$phone,$fax,$email,$contact,$website,$size,$logoname,$type,$userid){
		$this->load->database();
		$sql="INSERT INTO companies(
            				  com_name,
            				  com_desc,
            				  com_address,
            				  com_phone,
            				  com_fax,
            				  com_email,
            				  com_contactname,
            				  com_website,
            				  com_logo_size,
            				  com_logo_name,
            				  com_logo_type,
            				  user_create
            				  ) 
            	  VALUES('".pg_escape_string($name)."',
		            	  '".pg_escape_string($desc)."',
		            	  '".pg_escape_string($address)."',
		            	  '".pg_escape_string($phone)."',
		            	  '".pg_escape_string($fax)."',
		            	  '".pg_escape_string($email)."',
		            	  '".pg_escape_string($contact)."',
		            	  '".pg_escape_string($website)."',
		            	  ".$size.",
		            	  '".pg_escape_string($logoname)."',
		            	  '".pg_escape_string($type)."',
		            	  ".$userid.")";
            		$this->db->query($sql);
	}
	function updateCompany($comid,$name,$desc,$address,$phone,$fax,$email,$contact,$website){
		$this->load->database();
		$sql="UPDATE companies 
				SET com_name='".pg_escape_string($name)."',
				com_desc='".pg_escape_string($desc)."',
				com_address='".pg_escape_string($address)."',
				com_phone='".pg_escape_string($phone)."',
				com_fax='".pg_escape_string($fax)."',
				com_email='".pg_escape_string($email)."',
				com_contactname='".pg_escape_string($contact)."',
				com_website='".pg_escape_string($website)."' 
				WHERE com_id=".$comid;
            	$this->db->query($sql);
	}
	/* End of Company */
	
	/* Announcement */
	public function selectMaxAnnounID(){
		$this->load->database();
		$Z_USER = Zsession::getUserFromSession();
		$maxid=1;
		$sql="SELECT MAX(announ_id) AS announ_id FROM announcements WHERE user_post=".$Z_USER["user_id"];
		$rst=$this->db->query($sql);
		foreach ($rst->result_array() as $record){
			$maxid=($record["announ_id"]!="")?$record["announ_id"]:$maxid;
		}
		return $maxid;
	}
	function selectAnnouncementByUserId($user_id){
		$this->load->database();
		$sql="SELECT a.announ_id,
		        			 c.com_id,
		        			 c.com_name,
		        			 c.com_desc,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo,
		            		 c.com_logo_type,
		            		 a.announ_id,
		            		 a.user_post,
		            		 a.date_post,
		        			 a.date_close
		            	FROM companies AS c 
          		 		INNER JOIN announcements AS a 
          		  		ON c.com_id=a.com_id 
          		  	   WHERE a.user_post=".$user_id." 
          		  	ORDER BY a.announ_id DESC";
        return $this->db->query($sql);
	}
	function selectAnnouncement($ann_id){
		$this->load->database();
		$condition=($ann_id==0)?"":"WHERE a.announ_id=".$ann_id;
		$sql="SELECT a.announ_id,
		        			 c.com_id,
		        			 c.com_name,
		        			 c.com_desc,
		        			 c.com_contactname,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo,
		            		 c.com_address,
		            		 c.com_phone,
		            		 c.com_fax,
		            		 c.com_email,
		            		 c.com_website,
		            		 c.com_logo_type,
		            		 a.announ_id,
		            		 a.announ_notice,
		            		 a.user_post,
		            		 a.date_post,
		        			 a.date_close
		            	FROM companies AS c 
          		 		INNER JOIN announcements AS a 
          		  		ON c.com_id=a.com_id ".$condition;
        return $this->db->query($sql);
	}
	
	function search_job_result($category,$location,$num, $offset){
		$this->load->database();
		
		$sqlstatement="SELECT DISTINCT a.announ_id,
				        	  c.com_id,
				        	  c.com_name,
				        	  c.com_logo,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo_type,
				        	  a.date_close
				         FROM companies AS c 
				   INNER JOIN announcements AS a 
				           ON c.com_id=a.com_id 
				   INNER JOIN titles AS t 
				           ON a.announ_id=t.announ_id 
				   INNER JOIN titles_categories as tc
				   		   ON t.title_id=tc.title_id
				   INNER JOIN titles_locations as tl
				           ON t.title_id=tl.title_id ";
          	  
		if($offset==""){
			if($category=='0' && $location=='0')
			{
				$sql=$sqlstatement."ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
			elseif($category=='0' && $location<>'0'){
				$sql=$sqlstatement."WHERE tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
			elseif($category<>'0' && $location=='0'){
				$sql=$sqlstatement."WHERE tc.cate_id=".$category." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
			else{//if($cate<>'0' && $location<>'0')
				$sql=$sqlstatement."WHERE tc.cate_id=".$category." AND tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
        }else {//$offset <> ''
        	if($category=='0' && $location=='0'){
					$sql=$sqlstatement."ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;
				}
				elseif($category=='0' && $location<>'0'){
					$sql=$sqlstatement."WHERE tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;;
				}
				elseif($category<>'0' && $location=='0'){
					$sql=$sqlstatement."WHERE tc.cate_id=".$category." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;;
				}
				else{//if($cate<>'0' && $location<>'0')
					$sql=$sqlstatement."WHERE tc.cate_id=".$category." AND tl.loc_id=".$location." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;;
				}
        }
        return $this->db->query($sql);
	}
	
	function selectJobByCategoryAndLocation($category,$location){
		$this->load->database();
		
		$sqlstatement="SELECT DISTINCT a.announ_id,
				        	  c.com_id,
				        	  c.com_name,
				        	  c.com_logo,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo_type,
				        	  a.date_close
				         FROM companies AS c 
				   INNER JOIN announcements AS a 
				           ON c.com_id=a.com_id 
				   INNER JOIN titles AS t 
				           ON a.announ_id=t.announ_id 
				   INNER JOIN titles_categories as tc
				   		   ON t.title_id=tc.title_id
				   INNER JOIN titles_locations as tl
				           ON t.title_id=tl.title_id ";
          	  
			if($category=='0' && $location=='0')
			{
				$sql=$sqlstatement."ORDER BY a.announ_id DESC";
			}
			elseif($category=='0' && $location<>'0'){
				$sql=$sqlstatement."WHERE tl.loc_id=".$location." ORDER BY a.announ_id DESC";
			}
			elseif($category<>'0' && $location=='0'){
				$sql=$sqlstatement."WHERE tc.cate_id=".$category." ORDER BY a.announ_id DESC";
			}
			else{//if($cate<>'0' && $location<>'0')
				$sql=$sqlstatement."WHERE tc.cate_id=".$category." AND tl.loc_id=".$location." ORDER BY a.announ_id DESC";
			}
        return $this->db->query($sql);
	}
	
	function selectAnnouncementRecord($num, $offset){
		$this->load->database();
		$sqlstatement="SELECT a.announ_id,
		        			 c.com_id,
		        			 c.com_name,
		            		 c.com_logo_size,
		            		 c.com_logo_name,
		            		 c.com_logo,
		            		 c.com_logo_type,
		        			 a.date_close
		            	FROM companies AS c 
	          INNER JOIN announcements AS a 
	          		  ON c.com_id=a.com_id 
	          		  ORDER BY a.announ_id DESC
          		   LIMIT ".$num;
		if($offset==""){
            $sql=$sqlstatement." OFFSET 0";
        }else {
        	 $sql=$sqlstatement." OFFSET ". $offset;
        }
        return $this->db->query($sql);
	}
	public function insertAnnouncement($notice,$date_close,$com_id){
		$this->load->database();
		$Z_USER = Zsession::getUserFromSession();
		$sql="INSERT INTO announcements (announ_notice,date_post,date_close,com_id,user_post) VALUES ('".pg_escape_string($notice)."',NOW(),'".$date_close."',".$com_id.",".$Z_USER["user_id"].")";
		$this->db->query($sql);
	}
	public function updateAnnouncement($annid,$notice,$closingdate){
		$this->load->database();
		$sql="UPDATE announcements SET announ_notice='".pg_escape_string($notice)."',date_close='".$closingdate."' WHERE announ_id=".$annid;
		$this->db->query($sql);
	}
	public function deleteAnnouncement($ann_id){
		$this->load->database();
		$sql="DELETE FROM announcements WHERE announ_id=".$ann_id;
		$this->db->query($sql);
	}
	/* End of Announcement */
	
	public function insertTitle($ann_id,$title_desc){
		$this->load->database();
		$sql="INSERT INTO titles (announ_id,title_desc) VALUES (".$ann_id.",'".$title_desc."')";
		$this->db->query($sql);
	}
	
	/* JOB PAGE */
	function get_announ_by_announid($announid){
		$this->load->database();
		$sql="SELECT * FROM announcements WHERE announ_id=".$announid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	
	function selectLatestJobAnnouncements($num_row){
		$this->load->database();
        $sql="SELECT a.announ_id,
        			 c.com_id,
        			 c.com_name,
        			 c.com_desc,
            		 c.com_logo_size,
            		 c.com_logo_name,
            		 c.com_logo,
            		 c.com_logo_type,
        			 a.date_post,
        			 a.date_close
            	FROM announcements AS a 
          INNER JOIN companies AS c 
          		  ON a.com_id=c.com_id 
          		  ORDER BY a.announ_id DESC 
          		  LIMIT ".$num_row;
        return $this->db->query($sql);
	}
	/* END OF JOB PAGE */
	
	function getJobHit($announid){
		$this->load->database();
		$sql="SELECT *
				FROM hit_jobs WHERE announ_id=".$announid;
		return $this->db->query($sql);
	}
}

?>